By Garrett J. Suhm
The client/server revolution has brought many advances to modern computing. Improved interfaces, increased capabilities, and better integration have resulted in a superior environment over traditional host-based approaches. But implementing a system via a client/server paradigm can pose some unique problems. For example, you may get poor performance by running resource-intensive tasks, such as large reports, from the client. Another common problem is a large number of clients with insufficient memory to run Oracle Forms and Reports simultaneously. Fortunately, there is a solution. By running reports from the server, which tends to be a more robust platform, you can increase the potential pool of machines with client/server capability.
Oracle7 comes with some handy tools that will allow you to initiate server jobs from the client. A built-in package called DBMS_PIPE allows sessions to send and receive messages asynchronously. Oracle Pipes and Developer/2000 can be a powerful combination. Some practical applications include:
Here's how it works. A pipe is essentially a queue of messages. The client (writer) first packages a message and then puts the message into the queue. The server then reads the queue and unpacks the message for use. Because you can call a PL/SQL package over SQL*Net, any client that supports SQL*Net can use Oracle Pipes to make calls to the server. Make sure you run the DBMSPIPE.SQL file (as user SYS) in the Oracle RDBMS directory and grant execute privilege on the DBMS_PIPE package to anyone who will make these calls.
When you initially implement a client/server format, your first instinct may be to move all the applications onto the client. However, a variety of scenarios could make this impractical. Some of these are:
An alternative strategy is to call a pipe from a stored procedure to do the work on the server. We've found that this technique allows an effective client/server implementation using either 8 Mb PC compatibles or Macintoshes as clients. To use this method, you'll need two components: a process on the server to receive the messages and a function on the client to send messages.
For this technique to be effective, the server must be capable of running products that can produce the desired output. Oracle Reports is a good candidate, and it runs on most UNIX platforms and VMS. You'll also need either SQL*Plus or a compiler that supports both Oracle and external system calls. We'll use SQL*Plus in our example.
The first step is to develop a procedure that will receive messages from your clients. Its only job is to pass the message it receives to the server operating system for processing. This method allows you to write a variety of specialized functions that can do different tasks. Your Forms application (or any other product capable of calling stored procedures) can then call each function as needed. The key here is to keep the procedure very simple. For example, the procedure in Figure A waits for a designated amount of time before forwarding messages. Note that in our example, the timeout period is very large; this allows the function to wait until a message is received before using any server resources.
--This function waits for messages from the BATCH_SERVER pipe
create or replace function batch_monitor(batch_command in varchar2) return number is
status number := 0;
begin
-- Wait a very long time and
pass pipe message back to
-- calling program
-- the second parameter is the time-out period
status :=dbms_pipe.receive_message('BATCH_SERVER',8000000);
-- Unpack the incoming message
dbms_pipe.unpack_message(batch_command);
return(status);
end;
/
The next step is to create an interface between Oracle and the operating system. In the following simple example, we use SQL*Plus. This technique is very easy to implement, and you can use it successfully for low-volume requirements. Since SQL*Plus restarts and reconnects with Oracle every time you call a report, it's not a good idea to use this particular code with a high-volume system--but you can implement the same functionality in C without leaving the program or breaking the connection for each job. The script in Figure B is specific to VMS DCL, but by using a UNIX shell script you can accomplish the same thing.
$!Fire_batch.com
$! Calls the fire_batch.sql script,
$! and then runs the resulting command file and loops again
$again:
$ sqlplus -s / @fire_batch
$ @run
$ purge run.com
$ goto again
Notice that a call is made to a SQL script called fire_batch. This is a call to the stored procedure that waits for a message on your BATCH_SERVER pipe. See Figure C for the code listing on the stored procedure.
--fire_batch.sql
-- Call the Batch_monitor procedure and print the result to the run.com file
set term off
set linesize 250
set pages 0
variable batch_command varchar2(250)
variable errcode number;
execute :errcode := Batch_monitor(:batch_command);
spool run.com
print batch_command
spool off
exit
Although the Batch_monitor function normally returns an error code, we ignored the error code for the purposes of our example. Since we use a continuous loop in this program, a message returning an error won't terminate the process.
Oracle Forms can call stored procedures in just about any trigger. We usually use either buttons or menus attached to the form to initiate a report. For example, you could create a button and attach a WHEN-BUTTON-PRESSED trigger to it. You can then program the trigger to print a report using the following commands:
declare
my_printer varchar2(40);
begin
my_printer := Send_report ('some_report','my_param=bubba');
message('Report has been sent to '||my_printer);
end;
This trigger refers to the stored function send_report shown in Figure D. This function passes the command needed by the operating system (in this case, VMS) to start Oracle Reports with the appropriate parameters.
-- Create a string to send to the Operating System to run a batch report
create or replace function send_report(report_name varchar2, parameter_values varchar2)
return varchar2 is
current_user varchar2(12);
status number;
report_command varchar2(1000);
p_code number;
selected_printer varchar2(40);
begin
select user into current_user from sys.dual;
-- Assuming a table of the user's
-- preferred printers
select my_printer
into selected_printer
from user_printers
where user_id = user;
-- Build string to pass to the Batch_monitor function
report_command :=
'$submit/name='||current_user||
'batch_report/ param=('||current_user||','||selected_printer||','|| report_name||',"'||parameter_values||'")';
-- send message to batch_monitor
dbms_pipe.pack_message(report_command);
dbms_output.put_line(report_command);
status := dbms_pipe.send_message('BATCH_SERVER');
if status <> 0
then
raise_application_error(-20001,'Pipe Package Error');
end if;
return(selected_printer);
end;
The trigger also references a table that stores the users' printer locations. If the printers used by the server are not always the same as the ones used by the client, you'll need to build a form to maintain a table similar to ours. Instead of using the Chooser for Macintosh or Print Manager in Windows, your clients will need to use your printer form. As long as the selected printer is on the network, you can probably print to it from your host.
Oracle Pipes isn't the cure-all solution for everyone. There is still no easy way to get feedback from the jobs once they've started running. You also lose the ability to preview reports (unless you use Motif and an X server on the clients). But in spite of these limitations, Oracle Pipes and Developer/2000 can be a powerful combination. With a little creativity, you will surely find many ways to use this tool to exploit the resources at your disposal.
[Return to Index for Exploring Oracle Developer/2000 and Designer/2000 - January 1996]
Copyright (c) 1996 The Cobb Group, a division of Ziff-Davis Publishing Company. All rights reserved.
Reproduction in whole or in part in any form or medium without express written permission of Ziff-Davis
Publishing Company is prohibited. The Cobb Group and The Cobb Group logo are trademarks of
Ziff-Davis Publishing Company.